import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_四大经济区域学历分布下_毕业生就业人数及占比(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    四大经济区域学历分布下-毕业生就业人数及占比 - 指标计算函数
    
    ## 指标说明
    该指标用于统计四大经济区域(four_zone)下不同学历(education)毕业生的就业人数及占比情况。
    主要包含两个子指标：
    1. 各区域各学历下的就业人数分布和就业占比
    2. 各学历下的就业人数分布和就业占比
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": {
            "region_education_stats": [
                {
                    "region": "东部地区",
                    "education": "本科",
                    "total_count": 100,
                    "employed_count": 80,
                    "employment_rate": 0.8
                }
            ],
            "education_stats": [
                {
                    "education": "本科",
                    "total_count": 200,
                    "employed_count": 160,
                    "employment_rate": 0.8
                }
            ]
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 四大经济区域学历分布下-毕业生就业人数及占比, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, dy_target_items, split_tb_paper 
        FROM dp_njc.client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询各区域各学历总毕业生人数
        total_sql = f"""
        SELECT
            four_zone as region,
            education,
            count(*) as total_count
        FROM
            dp_njc.dim_client_target_baseinfo_student_calc_{item_year}
        WHERE shard_tb_key = %s and four_zone != ''
        GROUP BY four_zone, education
        """
        total_results = db.fetchall(total_sql, (shard_tb_key,))
        if not total_results:
            raise ValueError("未找到毕业生数据")
        
        logger.info(f"查询到总毕业生人数: {len(total_results)}条记录")

        # 4. 查询各区域各学历就业人数
        employed_sql = f"""
        SELECT
            four_zone as region,
            education,
            count(*) as employed_count
        FROM
            dp_njc.dim_client_target_baseinfo_student_calc_{item_year}
        WHERE shard_tb_key = %s and four_zone != ''
        and grad_dest_merge in ('单位就业','自主创业','自由职业','升学')
        GROUP BY four_zone, education
        """
        employed_results = db.fetchall(employed_sql, (shard_tb_key,))
        if not employed_results:
            raise ValueError("未找到就业毕业生数据")
        
        logger.info(f"查询到就业毕业生人数: {len(employed_results)}条记录")

        # 5. 合并数据并计算就业率
        region_education_stats = []
        education_stats_dict = {}

        # 5.1 处理区域维度数据
        for total_item in total_results:
            region = total_item['region']
            education = total_item['education']
            total_count = total_item['total_count']
            
            # 查找对应就业人数
            employed_item = next(
                (item for item in employed_results 
                 if item['region'] == region and item['education'] == education),
                None
            )
            
            employed_count = employed_item['employed_count'] if employed_item else 0
            employment_rate = employed_count / total_count if total_count > 0 else 0
            
            region_education_stats.append({
                "region": region,
                "education": education,
                "total_count": total_count,
                "employed_count": employed_count,
                "employment_rate": round(employment_rate, 4)
            })

            # 5.2 汇总学历维度数据
            if education not in education_stats_dict:
                education_stats_dict[education] = {
                    "total_count": 0,
                    "employed_count": 0
                }
            
            education_stats_dict[education]["total_count"] += total_count
            education_stats_dict[education]["employed_count"] += employed_count

        # 5.3 处理学历维度数据
        education_stats = []
        for education, counts in education_stats_dict.items():
            total = counts["total_count"]
            employed = counts["employed_count"]
            rate = employed / total if total > 0 else 0
            
            education_stats.append({
                "education": education,
                "total_count": total,
                "employed_count": employed,
                "employment_rate": round(rate, 4)
            })

        logger.info(f"指标 '四大经济区域学历分布下-毕业生就业人数及占比' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": {
                "region_education_stats": region_education_stats,
                "education_stats": education_stats
            }
        }

    except Exception as e:
        logger.error(f"计算指标 '四大经济区域学历分布下-毕业生就业人数及占比' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 四大经济区域学历分布下-毕业生就业人数及占比",
            "code": 500,
            "error": str(e)
        }